<?php
/**
 * 图片上传
 */
function admin_upload($exts = ['jpg' , 'gif' , 'png' , 'jpeg']) {
    $upload = new \Think\Upload();// 实例化上传类
    $upload->maxSize = 3145728;// 设置附件上传大小
    $upload->exts = $exts;// 设置附件上传类型
    $upload->rootPath = './uploads/'; // 设置附件上传根目录
    $upload->savePath = ''; // 设置附件上传（子）目录
    // 上传文件 
    $info = $upload->upload();

    return $info;
}
//excel导入数据方法
function excel_import($filename , $exts = 'xls') {
    //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
    import("Common.PHPExcel.PHPExcel" , '' , '.php');
    //创建PHPExcel对象，注意，不能少了\
    $PHPExcel = new \PHPExcel();
    //如果excel文件后缀名为.xls，导入这个类
    if ($exts == 'xls') {
        import("Common.PHPExcel.PHPExcel.Reader.Excel5" , '' , '.php');
        $PHPReader = new \PHPExcel_Reader_Excel5();
    } else if ($exts == 'xlsx') {
        import("Common.PHPExcel.phpexcel.Reader.Excel2007" , '' , '.php');
        $PHPReader = new \PHPExcel_Reader_Excel2007();
    }
    //载入文件
    $PHPExcel = $PHPReader->load($filename);
    //获取表中的第一个工作表，如果要获取第二个，把0改为1，依次类推
    $currentSheet = $PHPExcel->getSheet(0);
    //获取总列数
    $allColumn = $currentSheet->getHighestColumn();
    //获取总行数
    $allRow = $currentSheet->getHighestRow();
    //循环获取表中的数据，$currentRow表示当前行，从哪行开始读取数据，索引值从0开始
    for ($currentRow = 1; $currentRow <= $allRow; $currentRow++) {
        //从哪列开始，A表示第一列
        $temp = [];
        for ($currentColumn = 'A'; $currentColumn <= $allColumn; $currentColumn++) {
            //数据坐标
            $address = $currentColumn . $currentRow;
            //读取到的数据，保存到数组$arr中
            $cell = $currentSheet->getCell($address)->getValue();
            //$cell = $data[$currentRow][$currentColumn];
            if ($cell instanceof PHPExcel_RichText) {
                $cell = $cell->__toString();
            }
            $temp[] = $cell;
        }
        $data[] = $temp;
    }

    return $data;
}
//excel导出
function excel_export($data) {
    //    include 'PHPExcel.php';
    //    include 'PHPExcel/Writer/Excel2007.php';
    //导入PHPExcel类库，因为PHPExcel没有用命名空间，只能inport导入
    import("Common.PHPExcel.PHPExcel" , '' , '.php');
    import("Common.PHPExcel.PHPExcel.Writer.Excel2007" , '' , '.php');
    //创建PHPExcel对象，注意，不能少了\
    $objPHPExcel = new \PHPExcel();
    //或者$objWriter = new PHPExcel_Writer_Excel5($objPHPExcel); 非2007格式
    //    设置当前的sheet
    $objPHPExcel->setActiveSheetIndex(0);
    //    设置sheet的name
    $objPHPExcel->getActiveSheet()->setTitle('Data');
    //    设置单元格的值
    //示例
//    $objPHPExcel->getActiveSheet()->setCellValue('A1' , 'String');
//    $objPHPExcel->getActiveSheet()->setCellValue('A2' , 12);
//    $objPHPExcel->getActiveSheet()->setCellValue('A3' , TRUE);
//    $objPHPExcel->getActiveSheet()->setCellValue('C5' , '=SUM(C2:C4)');
//    $objPHPExcel->getActiveSheet()->setCellValue('B8' , '=MIN(B2:C5)');
    $letter=1;
    $num=1;
    foreach($data[0] as $k=>$v){
        $index=convert_10to26($letter).$num;
        $objPHPExcel->getActiveSheet()->setCellValue($index, $k);
        $letter++;
    }
    $num++;
    foreach($data as $k=>$v){
        $letter=1;
        foreach($data[$k] as $kk=>$vv){
            $index=convert_10to26($letter).$num;
            if($vv=='NULL'){
                $vv='';
            }
            $objPHPExcel->getActiveSheet()->setCellValue($index, $vv);
            $letter++;
        }
        $num++;
    }
    //    保存excel—2007格式
    $objWriter = new \PHPExcel_Writer_Excel2007($objPHPExcel);
    $filename = 'export_' . date('YmdHis') . ".xlsx";
    $filepath = "uploads/excel/" . $filename;
    $objWriter->save($filepath);
    return $filepath;
}
//直接输出文件
function ouput_Browser($filename , $exts = 'xlsx') {
    //        直接输出到浏览器
    //如果excel文件后缀名为.xls，导入这个类
    if ($exts == 'xls') {
        import("Common.PHPExcel.PHPExcel.Reader.Excel5" , '' , '.php');
        $PHPReader = new \PHPExcel_Reader_Excel5();
    } else if ($exts == 'xlsx') {
        import("Common.PHPExcel.phpexcel.Reader.Excel2007" , '' , '.php');
        $PHPReader = new \PHPExcel_Reader_Excel2007();
    }
    //载入文件
    $objPHPExcel = $PHPReader->load($filename);
    $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
    header("Pragma: public");
    header("Expires: 0");
    header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
    header("Content-Type:application/force-download");
    header("Content-Type:application/vnd.ms-execl");
    header("Content-Type:application/octet-stream");
    header("Content-Type:application/download");;
    header('Content-Disposition:attachment;filename="output_'.date('Ymd_His').'.xls"');
    header("Content-Transfer-Encoding:binary");
    $objWriter->save('php://output');
}
//10进制转换26进制
//1=A,27=AA
function convert_10to26($x){
    if($x<=26){
        return  chr($x+ord('A')-1);
    }
    $m=floor($x/26);
    $n=$x-$m*26;
    $x=($x-$n)/26;
    return convert_10to26($x).chr($n+ord('A')-1);
}
//excel时间格式转换
function convert_etimetoptime($date, $time=false){
//    $t = 41039.666666667; //读取到的值
//    $n = intval(($t - 25569) * 3600 * 24); //转换成1970年以来的秒数
//    return gmdate('Y-m-d H:i:s', $n);//格式化时间
    if(is_numeric($date)){
        $jd = GregorianToJD(1, 1, 1970);
        $gregorian = JDToGregorian($jd+intval($date)-25569);
        $date = explode('/',$gregorian);
        $date_str = str_pad($date[2],4,'0', STR_PAD_LEFT)
            ."-".str_pad($date[0],2,'0', STR_PAD_LEFT)
            ."-".str_pad($date[1],2,'0', STR_PAD_LEFT)
            .($time?" 00:00:00":'');
        return $date_str;
    }
    return $date;
}